

package pe.sonrisas.tools.mysql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import pe.sonrisas.tools.dao.PacienteDAO;
import pe.sonrisas.tools.dao.UbigeoDAO;

public class UbigeoDAOMysql implements UbigeoDAO{

    Conexion_BD co=new Conexion_BD();
    ResultSet rs = null;
  
    public ArrayList<ArrayList> listarDeptos(){

            ArrayList<ArrayList> v = new ArrayList<ArrayList>();
            try {

            co.Conectar();
            
                 rs = co.Consultar("select * from  ubigeo where codprov = '0' and coddist = '0'");
                               
                

            if (rs.first()) {
                while (!rs.isAfterLast()) {
                    ArrayList registro = new ArrayList();
                    registro.add(rs.getString(2));
                    registro.add(rs.getString(5));
                   
                    v.add(registro);
                    rs.next();
                }
            }

            try {
                co.Desconectar();
            } catch (Exception ex) {
                Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            return v;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

            return v;

        }


        public ArrayList<ArrayList> listarProvincias(int codDepto){

            ArrayList<ArrayList> v = new ArrayList<ArrayList>();
            try {

            co.Conectar();

                rs = co.Consultar("select nombre from ubigeo where CODDEPTO = '"+ codDepto +"' and coddist = '0' and CODPROV <> '0'");



            if (rs.first()) {
                while (!rs.isAfterLast()) {
                    ArrayList registro = new ArrayList();
                    registro.add(rs.getString(1));
                    v.add(registro);
                    rs.next();
                }
            }

            try {
                co.Desconectar();
            } catch (Exception ex) {
                Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            return v;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

            return v;

        }


         public ArrayList<ArrayList> listarDistritos(int codDepto,int codProvincia){

            ArrayList<ArrayList> v = new ArrayList<ArrayList>();
            try {

            co.Conectar();

                rs = co.Consultar("select nombre from ubigeo where CODDEPTO = '"+ codDepto +"'  and CODPROV = '"+codProvincia+"' and coddist <> '0'");



            if (rs.first()) {
                while (!rs.isAfterLast()) {
                    ArrayList registro = new ArrayList();
                    registro.add(rs.getString(1));
                    v.add(registro);
                    rs.next();
                }
            }

            try {
                co.Desconectar();
            } catch (Exception ex) {
                Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
            return v;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(PacienteDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

            return v;

        }



         public int dameIdUbigeo(int coddpto,int codprov,int coddist){
             int idUbigeo = 0;

                try {
            co.Conectar();
           rs = co.Consultar("select idUbigeo from UBIGEO where CODDEPTO = '"+coddpto+"' and CODPROV = '" + codprov +"' and CODDIST = '"+coddist+"'");
            if (rs.first()) {
               idUbigeo = Integer.parseInt(rs.getString(1));
            }
            
                } catch (ClassNotFoundException ex) {
            Logger.getLogger(UbigeoDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
                } catch (SQLException ex) {
            Logger.getLogger(UbigeoDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
                }


                
                return idUbigeo;


         }



        public ArrayList<ArrayList> dameUbicacion(int id_ubigeo){
        ArrayList<ArrayList> v = new ArrayList<ArrayList>();
             try {

            co.Conectar();
            rs = co.Consultar("select CODDEPTO,CODPROV,CODDIST from UBIGEO where idUbigeo ='" + id_ubigeo+"'");
            if (rs.first()) {
                while (!rs.isAfterLast()) {
                    ArrayList registro = new ArrayList();
                    registro.add(rs.getInt(1));
                    registro.add(rs.getInt(2));
                    registro.add(rs.getInt(3));
                    v.add(registro);
                    rs.next();
                }
            }
            try {
                co.Desconectar();
            } catch (Exception ex) {
                Logger.getLogger(UbigeoDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UbigeoDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(UbigeoDAOMysql.class.getName()).log(Level.SEVERE, null, ex);
        }
            return v;
             }

}
